package edu.lingnan.rili.mapper;

import edu.lingnan.rili.pojo.Uploading;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.springframework.stereotype.Repository;
import edu.lingnan.rili.vo.PagingQuery;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author 陈世杰
 * @since 2022-01-23
 */
@Repository
public interface UploadingMapper extends BaseMapper<Uploading> {


    /**
     * 这是一个其他比赛公示页面的模糊查询
     * @param startNum 开始的行数
     * @param endNum 查询的条数
     * @return
     */
    @Select("select * from tb_uploading  where uploading_type='0' " +
            "and state_id='1' order by uploading_time desc limit #{startNum},#{endNum};")
    List<Uploading> getOtherGamesUploadingInfos
    (@Param("startNum") int startNum, @Param("endNum") int endNum);
    /**
     * 这是一个其他加分公示分页查询的方法
     * @param startNum 开始的行数
     * @param endNum 查询的条数
     * @return
     */
    @Select("select * from tb_uploading  where uploading_type='1' and state_id='1' limit #{startNum},#{endNum};")
    List<Uploading> getOtherPointsUploadingInfos
    (@Param("startNum") int startNum, @Param("endNum") int endNum);

    /**
     * 这是一个其他比赛公示页面的模糊查询方法
     * @param keywords 关键字
     * @return
     */
    @Select("select a1 as usersName,a2 as userClass,a3 as userGrade,a10 as userMajor," +
            "a4 as userCollege,a5 as usersId,a6 as gameName,a7 as score,a8 as addtype" +
            " from (select c.users_name as a1,c.user_class as a2," +
            "c.user_grade as a3,c.user_college as a4,c.user_major as a10," +
            "c.users_id as a5,A.game_name as a6,b.score as a7,b.addtype as a8,a.uploading_type as a9\n" +
            "from tb_match C,tb_uploading A,tb_awarded B \n" +
            "where a.uploading_id=b.uploading_id and\n" +
            "a.users_id=c.users_id and a.state_id=1 and a.uploading_type=0) as sb\n" +
            "where a1 like concat('%',#{keywords},'%') or a2 like concat('%',#{keywords},'%') " +
            "or a3 LIKE concat('%',#{keywords},'%')" +
            " or a4 LIKE concat('%',#{keywords},'%')" +
            " or a5 LIKE concat('%',#{keywords},'%')\n" +
            "or a6 like concat('%',#{keywords},'%')" +
            "or a8 LIKE concat('%',#{keywords},'%')" +
            "or a9 LIKE concat('%',#{keywords},'%')"+
            "or a10 LIKE concat('%',#{keywords},'%');")
    List<PagingQuery> fuzzyQueryOfOtherCompetitionPublicityPages(@Param("keywords") String keywords);

    /**
     * 这是一个其他加分公示页面的模糊查询方法
     * @param keywords 关键字
     * @return
     */
    @Select("select a1 as usersName,a2 as userClass,a3 as userGrade,a10 as userMajor," +
            "a4 as userCollege,a5 as usersId,a6 as gameName,a7 as score,a8 as addtype" +
            " from (select c.users_name as a1,c.user_class as a2," +
            "c.user_grade as a3,c.user_college as a4,c.user_major as a10," +
            "c.users_id as a5,A.game_name as a6,b.score as a7,b.addtype as a8,a.uploading_type as a9\n" +
            "from tb_match C,tb_uploading A,tb_awarded B \n" +
            "where a.uploading_id=b.uploading_id and\n" +
            "a.users_id=c.users_id and a.state_id=1 and a.uploading_type=1) as sb\n" +
            "where a1 like concat('%',#{keywords},'%') or a2 like concat('%',#{keywords},'%') " +
            "or a3 LIKE concat('%',#{keywords},'%')" +
            " or a4 LIKE concat('%',#{keywords},'%')" +
            " or a5 LIKE concat('%',#{keywords},'%')\n" +
            "or a6 like concat('%',#{keywords},'%')" +
            "or a8 LIKE concat('%',#{keywords},'%')" +
            "or a9 LIKE concat('%',#{keywords},'%')"+
            "or a10 LIKE concat('%',#{keywords},'%');")
    List<PagingQuery> fuzzyQueryOfOtherBonusPublicityPages(@Param("keywords") String keywords);
}
